/**
 * Load the APIs and run sendQuery when the load is complete
 */
_IG_RegisterOnloadHandler( function(){
	google.load( 'jquery', '1.6' );
	google.load( "visualization", "1" );
	google.setOnLoadCallback( ready );
} );

function ready(){
	/**
	 * Create a query (shaped by the Gadget's user preferences), then
	 * send it to the spreadsheet data source. Also give the name of a
	 * function ("handleQueryResponse") to run once the spreadsheet data
	 * is retrieved:
	 */	
	testGadget.prefs = new _IG_Prefs(); // ユーザー設定 
	testGadget.gadgetHelper = new google.visualization.GadgetHelper(); 
	testGadget.query = testGadget.gadgetHelper.createQueryFromPrefs( testGadget.prefs );
	testGadget.query.send( function( response ){
		
		// error
		if ( !testGadget.gadgetHelper.validateResponse( response ) ) return;
		
		testGadget.generateTable( response );
		testGadget.setOptions	( response );
		testGadget.setUiEventHandler();
	});
}

var testGadget = {
	prefs			: null,
	query			: null,
	gadgetHelper	: null,
	setUiEventHandler : function(){
		$( "select[ id ^= 'filter' ]" ).change( function(){
			testGadget.loadData();
		});
	},
	setOptions : function( response ){
	
		var arrReporter		= new Array();
		var arrStatus		= new Array();
		var arrCoder		= new Array();
		var arrSubject		= new Array();
		var arrReportDate	= new Array();
		var arrUpdateDate	= new Array();
		var data = response.getDataTable();
		for ( var row = 0; row < data.getNumberOfRows(); row++ ) {
			var reporter	= data.getFormattedValue( row, 1 );
			var status		= data.getFormattedValue( row, 2 );
			var coder		= data.getFormattedValue( row, 3 );
			var subject		= data.getFormattedValue( row, 4 );
			var reportDate	= data.getFormattedValue( row, 6 ).substr( 0, 10 ).replace( /\//g, '-' );
			var updateDate	= data.getFormattedValue( row, 7 ).substr( 0, 10 ).replace( /\//g, '-' );
			if( $.inArray( reporter		, arrReporter	) == -1 ) arrReporter	.push( reporter		);
			if( $.inArray( status		, arrStatus		) == -1 ) arrStatus		.push( status		);
			if( $.inArray( coder		, arrCoder		) == -1 ) arrCoder		.push( coder		);
			if( $.inArray( subject		, arrSubject	) == -1 ) arrSubject	.push( subject		);
			if( $.inArray( reportDate	, arrReportDate	) == -1 ) arrReportDate	.push( reportDate	);
			if( $.inArray( updateDate	, arrUpdateDate	) == -1 ) arrUpdateDate	.push( updateDate	);
		}
		
		for( var i in arrReporter	) { $( "#filterReporter"	).append( '<option>' + arrReporter	[i] + '</option>' ); }
		for( var i in arrStatus		) { $( "#filterStatus"		).append( '<option>' + arrStatus	[i] + '</option>' ); }
		for( var i in arrCoder		) { $( "#filterCoder"		).append( '<option>' + arrCoder		[i] + '</option>' ); }
		for( var i in arrSubject	) { $( "#filterSubject"		).append( '<option>' + arrSubject	[i] + '</option>' ); }
		for( var i in arrReportDate	) { $( "#filterReportDate"	).append( '<option>' + arrReportDate[i] + '</option>' ); }
		for( var i in arrUpdateDate	) { $( "#filterUpdateDate"	).append( '<option>' + arrUpdateDate[i] + '</option>' ); }
	},
	loadData : function(){
		
		var cond = new Array();
		if( $( "#filterReporter"	).val() != '--' ) cond.push( "B = '" 				+ $( "#filterReporter"		).val() + "'" );
		if( $( "#filterStatus"		).val() != '--' ) cond.push( "C = '" 				+ $( "#filterStatus"		).val() + "'" );
		if( $( "#filterCoder"		).val() != '--' ) cond.push( "D = '" 				+ $( "#filterCoder"			).val() + "'" );
		if( $( "#filterSubject"		).val() != '--' ) cond.push( "E = '" 				+ $( "#filterSubject"		).val() + "'" );
		if( $( "#filterReportDate"	).val() != '--' ) cond.push( "toDate( G ) = date '"	+ $( "#filterReportDate"	).val() + "'" );
		if( $( "#filterUpdateDate"	).val() != '--' ) cond.push( "toDate( H ) = date '"	+ $( "#filterUpdateDate"	).val() + "'" );
		
		var sql = 'select * ';
		if( cond.length ) sql += 'where ' + cond.join( ' and ' )

		this.query.setQuery( sql );
		this.query.send( function( response ){ testGadget.generateTable( response ); } );
	},
	generateTable : function( response ){
		
		// データ取得
		var data = response.getDataTable();
		
		// 元になるテーブル
		var table = $(	"<table class='borderTable size8pt'>" +
						"	<thead />" +
						"	<tbody />" +
						"	<tfoot />" +
						"</table>" );
		
		// 見出し行
		var thead = $( 'thead', table ).append( "<tr />" );
		for ( var col = 0; col < data.getNumberOfColumns(); col++ ){
			$( 'tr', thead ).append( '<th>' + data.getColumnLabel( col ) + '</th>' );
		}
		
		// 各行の処理
		var tbody = $( 'tbody', table );
		for ( var row = 0; row < data.getNumberOfRows(); row++ ) {
			
			tbody.append( '<tr />' );
			
			// 各列の処理
			for ( var col = 0; col < data.getNumberOfColumns(); col++ ) {
				// 行、列インデックスからデータ取得
				var value = data.getFormattedValue( row, col ).replace( '\n', '<br />' );	// 改行コードを br タグに変換
				$( 'tr:last', tbody ).append( '<td>' + value + '</td>' );
			}
		}
		
		// フッタ
		var tfoot = $( 'tfoot', table ).append( '<tr />' );
		$( 'tr', tfoot ).append( "<th class='right' colspan='" + data.getNumberOfColumns() + "'>" + data.getNumberOfRows() + "件</th>" );
		
		// 挿入
		$( '#tablediv' ).empty()			// loading画像を削除
						.append( table );
	},
	escapeHtml : function( text ){
		if (text == null) {
			return '';
		}
		return _hesc(text);
	}
};
